﻿using Dapper;
using OddUserManager.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading;

namespace OddUserManager.Data
{
    public class DapperBaseRepository<TEntity, TPrimaryKey>
    {
        private readonly string _connectionString;
        protected DapperBaseRepository()
        {
            _connectionString = DatabaseSettings.DefaultConnectionString;
        }

        protected IDbConnection GetOpenConnection()
        {
            IDbConnection connection = new SqlConnection(_connectionString);
            try
            {
                connection.Open();
            }
            catch (InvalidOperationException)
            {
                Thread.Sleep(5000);
                connection.Dispose();
                connection = GetOpenConnection();
            }
            return connection;
        }

        protected int Execute(CommandType commandType, string sql, object parameters = null)
        {
            using (var connection = GetOpenConnection())
            {
                return connection.Execute(sql, parameters, commandType: commandType);
            }
        }

        protected static PropertyContainer ParseProperties(TEntity obj)
        {
            var propertyContainer = new PropertyContainer();

            var typeName = typeof(TEntity).Name;
            var validKeyNames = new[] { "Id", string.Format("{0}Id", typeName), string.Format("{0}_Id", typeName) };

            var properties = typeof(TEntity).GetProperties();
            foreach (var property in properties)
            {
                // Skip reference types (but still include string!)
                if (property.PropertyType.IsClass && property.PropertyType != typeof(string))
                    continue;

                // Skip methods without a public setter
                if (property.GetSetMethod() == null)
                    continue;

                // Skip methods specifically ignored
                if (property.IsDefined(typeof(DapperIgnore), false))
                    continue;

                var name = property.Name;
                var value = typeof(TEntity).GetProperty(property.Name).GetValue(obj, null);

                if (property.IsDefined(typeof(DapperKey), false) || validKeyNames.Contains(name))
                {
                    propertyContainer.AddId(name, value);
                }
                else
                {
                    propertyContainer.AddValue(name, value);
                }
            }

            return propertyContainer;
        }

        private static string GetSqlPairs(IEnumerable<string> keys, string separator = ", ")
        {
            var pairs = keys.Select(key => string.Format("{0}=@{0}", key)).ToList();
            return string.Join(separator, pairs);
        }



        public bool Insert(TEntity entity)
        {
            bool isSuccess = false;
            var propertyContainer = ParseProperties(entity);
            var sql = $"insert into {typeof(TEntity).Name} ({string.Join(", ", propertyContainer.ValueNames)}) values (@{string.Join(", @", propertyContainer.ValueNames)})";
            using (var connection = GetOpenConnection())
            {
                connection.Execute(sql, propertyContainer.ValuePairs);
                isSuccess = true;
            }
            return isSuccess;
        }



        public bool Update(TEntity entity)
        {
            bool isSuccess = false;
            var propertyContainer = ParseProperties(entity);
            var sqlIdPairs = GetSqlPairs(propertyContainer.IdNames);
            var sqlValuePairs = GetSqlPairs(propertyContainer.ValueNames);
            var sql = $"update {typeof(TEntity).Name} set {sqlValuePairs} where {sqlIdPairs}";
            Execute(CommandType.Text, sql, propertyContainer.AllPairs);
            isSuccess = true;
            return isSuccess;
        }


        public TEntity Get(int id)
        {
            
            var sql = $"select * from {typeof(TEntity).Name}  where id={id}";
            using (var con = GetOpenConnection())
            {
                return con.Query<TEntity>(sql, null, null, true, 3000, null).FirstOrDefault();
            }
        }


        public IEnumerable<TEntity> GetAllList() {
            var sql = $"select * from {typeof(TEntity).Name}";
            using (var con = GetOpenConnection())
            {
                return con.Query<TEntity>(sql, null, null, true, 3000, null);
            }
        }

        public void Delete(TEntity entity)
        {
            var propertyContainer = ParseProperties(entity);
            var sqlIdPairs = GetSqlPairs(propertyContainer.IdNames);
            var sql = $"delete from {typeof(TEntity).Name}  where {sqlIdPairs}";
            Execute(CommandType.Text, sql, propertyContainer.IdPairs);
        }

        public void Delete(int id)
        {

            var sql = $"delete from {typeof(TEntity).Name}  where Id={id}";
            Execute(CommandType.Text, sql);
        }


        public void DeleteBatch(List<int> ids)
        {
            var sql = $"delete from {typeof(TEntity).Name}  where Id in({string.Join(",", ids)})";
            Execute(CommandType.Text, sql);
        }

        public void Delete(TPrimaryKey id, string tableName, string IdColumnName)
        {
            var sqlIdPairs = "" + IdColumnName + "=" + id;
            var sql = $"delete from {tableName}  where {sqlIdPairs}";
            Execute(CommandType.Text, sql, null);
        }

    }
    public class PropertyContainer
    {
        private readonly Dictionary<string, object> _ids;
        private readonly Dictionary<string, object> _values;

        #region Properties

        internal IEnumerable<string> IdNames
        {
            get { return _ids.Keys; }
        }

        internal IEnumerable<string> ValueNames
        {
            get { return _values.Keys; }
        }

        internal IEnumerable<string> AllNames
        {
            get { return _ids.Keys.Union(_values.Keys); }
        }

        internal IDictionary<string, object> IdPairs
        {
            get { return _ids; }
        }

        internal IDictionary<string, object> ValuePairs
        {
            get { return _values; }
        }

        internal IEnumerable<KeyValuePair<string, object>> AllPairs
        {
            get { return _ids.Concat(_values); }
        }

        #endregion

        #region Constructor

        internal PropertyContainer()
        {
            _ids = new Dictionary<string, object>();
            _values = new Dictionary<string, object>();
        }

        #endregion

        #region Methods

        internal void AddId(string name, object value)
        {
            _ids.Add(name, value);
        }

        internal void AddValue(string name, object value)
        {
            _values.Add(name, value);
        }

        #endregion
    }



    public static class DapperExtension
    {
        public static IEnumerable<TFirst> Map<TFirst, TSecond, TKey>
        (
        this Dapper.SqlMapper.GridReader reader,
        Func<TFirst, TKey> firstKey,
        Func<TSecond, TKey> secondKey,
        Action<TFirst, IEnumerable<TSecond>> addChildren
        )
        {
            var first = reader.Read<TFirst>().ToList();
            var childMap = reader
                .Read<TSecond>()
                .GroupBy(s => secondKey(s))
                .ToDictionary(g => g.Key, g => g.AsEnumerable());

            foreach (var item in first)
            {
                IEnumerable<TSecond> children;
                if (childMap.TryGetValue(firstKey(item), out children))
                {
                    addChildren(item, children);
                }
            }

            return first;
        }


        public static IEnumerable<TFirst> Map<TFirst, TSecond, TThred, TKey, TKey2>
       (
       this Dapper.SqlMapper.GridReader reader,
       Func<TFirst, TKey> firstKey,
       Func<TSecond, TKey> secondKey,
        Func<TSecond, TKey2> thredKey,
            Func<TThred, TKey2> fourKey,
       Action<TFirst, IEnumerable<TSecond>> addChildren,
        Action<TSecond, IEnumerable<TThred>> addChildren2
       )
        {
            var first = reader.Read<TFirst>().ToList();
            var second = reader.Read<TSecond>().ToList();
            var childMap = second.GroupBy(s => secondKey(s))
                .ToDictionary(g => g.Key, g => g.AsEnumerable());



            var childMap2 = reader
                .Read<TThred>()
                .GroupBy(s => fourKey(s))
                .ToDictionary(g => g.Key, g => g.AsEnumerable());


            foreach (var item in first)
            {
                IEnumerable<TSecond> children;
                if (childMap.TryGetValue(firstKey(item), out children))
                {
                    addChildren(item, children);
                }
            }

            foreach (var item in second)
            {
                IEnumerable<TThred> children;
                if (childMap2.TryGetValue(thredKey(item), out children))
                {
                    addChildren2(item, children);
                }
            }

            return first;
        }
    }
}
